SQL Server Query Tuning and Optimization by Benjamin Nevarez

Author:Benjamin Nevarez
Language: eng
Format: epub
Publisher: Packt Publishing Pvt Ltd
Published: 2022-07-22T00:00:00+00:00

Missing indexes

SQL Server provides a second approach that can help you find useful indexes for your existing queries. Although not as powerful as the DTA, this option, called the missing indexes feature, does not require the database administrator to decide when tuning is needed, to explicitly identify what workload represents the load to tune, or to run any tool. This is a lightweight feature that is always on and, like the DTA, was also introduced with SQL Server 2005. Let’s take a look at what it does.

During optimization, the query optimizer defines what the best indexes for a query are and, if these indexes don’t exist, it will make this index information available in the query XML plan (which is also available in a graphical plan in SQL Server Management Studio 2008 or later). Alternatively, it will aggregate this information for optimized queries since the instance was started, and make it all available on the sys.dm_db_missing_index DMVs. Note that just by displaying this information, the query optimizer is not only warning you that it might not be selecting an efficient plan, but it is also showing you which indexes may help to improve the performance of your query. In addition, database administrators and developers should be aware of the limitations of this feature, as described in “Limitations of the Missing Indexes Feature,” at http://msdn.microsoft.com/en-us/library/ms345485(v=sql.105).aspx.

So, with all that in mind, let’s take a quick look to see how this feature works. Create the dbo.SalesOrderDetail table on the AdventureWorks2019 database by running the following statement:

SELECT * INTO dbo.SalesOrderDetail

FROM Sales.SalesOrderDetail

Run the following query and request a graphical or XML execution plan:

SELECT * FROM dbo.SalesOrderDetail

WHERE SalesOrderID = 43670 AND SalesOrderDetailID > 112

This query could benefit from an index on the SalesOrderID and SalesOrderDetailID columns, but no missing indexes information is shown this time. One limitation of the missing indexes feature that this example has revealed is that it does not work with a trivial plan optimization. You can verify that this is a trivial plan by looking at the graphical plan properties, shown as Optimization Level TRIVIAL, or by looking at the XML plan, where StatementOptmLevel is shown as TRIVIAL. You can avoid trivial plan optimization in several ways, as explained in Chapter 3, The Query Optimizer. In our case, we’re just going to create a non-related index by running the following statement:

CREATE INDEX IX_ProductID ON dbo.SalesOrderDetail(ProductID)

What is significant about this is that, although the index created will not be used by our previous query, the query no longer qualifies for a trivial plan. Run the query again, and this time the XML plan will contain the following entry:

<MissingIndexes>

<MissingIndexGroup Impact=”99.7142”>

<MissingIndex Database=”[AdventureWorks2019]” Schema=”[dbo]”

Table=”[SalesOrderDetail]”>

<ColumnGroup Usage=”EQUALITY”>

<Column Name=”[SalesOrderID]” ColumnId=”1” />

</ColumnGroup>

<ColumnGroup Usage=”INEQUALITY”>

<Column Name=”[SalesOrderDetailID]” ColumnId=”2” />

</ColumnGroup>

</MissingIndex>

</MissingIndexGroup>

</MissingIndexes>

The MissingIndexes entry in the XML plan can show up to three groups—equality, inequality, and included—and the first two are shown in this example using the ColumnGroup attribute. The information contained in these groups can be used to create the missing index; the key of the index can be built by using the equality columns,

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.